package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.sql.SQLException;

import data.dao.UserHierarchyDAO;
import data.dao.UserHierarchyDAOException;
import data.dto.UserHierarchyDTO;
import data.searchdto.UserHierarchySearchDTO;

public class UserHierarchyDAOImpl implements UserHierarchyDAO {

	@Override
	public UserHierarchyDTO findHierarchy(
			UserHierarchySearchDTO userHierarchySearchDTO)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM hierarchy WHERE status = 1 AND usertype = ?";
		UserHierarchyDTO userHierarchy = null;

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, userHierarchySearchDTO.getUserType());
			
						ResultSet result = util.Database.getInstance().executeQuery(smt);
			
			if (result.next()){
				userHierarchy = getUserHierarchyDTO(result);
			}

			smt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return userHierarchy;
	}

	@Override
	public ArrayList<UserHierarchyDTO> findAllHierarchy()
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM hierarchy WHERE status = "
				+ util.Constants.STATUS_SHOW;
		ArrayList<UserHierarchyDTO> userHierarchies = new ArrayList<UserHierarchyDTO>();

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			ResultSet result = util.Database.getInstance().executeQuery(smt);

			while (result.next()) {
				userHierarchies.add(getUserHierarchyDTO(result));
			}

			smt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return userHierarchies;
	}

	@Override
	public void insertHierarchy(UserHierarchyDTO hierarchy)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO hierarchy (usertype, status) VALUES(?, ?)";

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, hierarchy.getUserType());
//			smt.setInt(2, hierarchy.getCreatedBy());
//			smt.setTimestamp(3, hierarchy.getCreatedOn());
//			smt.setInt(4, hierarchy.getModifiedBy());
//			smt.setTimestamp(5, hierarchy.getModifiedOn());
			smt.setShort(2, util.Constants.STATUS_SHOW);

			int rowcount = util.Database.getInstance().executeUpdate(smt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

			smt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void updateHierarchy(UserHierarchyDTO hierarchy)
			throws UserHierarchyDAOException {
		// TODO Auto-generated method stub
		String sql = "UPDATE hierarchy SET usertype = ?, status = ? where id = ?";

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, hierarchy.getUserType());
//			smt.setInt(2, hierarchy.getCreatedBy());
//			smt.setTimestamp(3, hierarchy.getCreatedOn());
//			smt.setInt(4, hierarchy.getModifiedBy());
//			smt.setTimestamp(5, hierarchy.getModifiedOn());
			smt.setShort(2, util.Constants.STATUS_SHOW);
			smt.setShort(3, hierarchy.getId());

			int rowcount = util.Database.getInstance().executeUpdate(smt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
			smt.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void deleteHierarchy(UserHierarchyDTO hierarchy)
			throws UserHierarchyDAOException, NotFoundException {
		// TODO Auto-generated method stub
		String sql = "UPDATE hierarchy SET status = ? where id = ?";

		try {
			PreparedStatement smt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			smt.setShort(1, util.Constants.STATUS_HIDDEN);
			smt.setShort(2, hierarchy.getId());

			int rowcount = util.Database.getInstance().executeUpdate(smt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
			smt.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private UserHierarchyDTO getUserHierarchyDTO(ResultSet rs) {
		UserHierarchyDTO userHierarchy = null;
		try {
//			userHierarchy = new UserHierarchyDTO(rs.getShort("id"),
//					rs.getShort("usertype"), rs.getInt("createdby"),
//					rs.getTimestamp("createdon"), rs.getInt("modifiedby"),
//					rs.getTimestamp("modifiedon"), rs.getShort("status"));
			userHierarchy = new UserHierarchyDTO(rs.getShort("id"),
					rs.getShort("usertype"), rs.getShort("status"));
		} catch (java.sql.SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e){
			e.printStackTrace();
		}

		return userHierarchy;
	}
}
